{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 金融 证券 图谱搭建"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "根据前面数据梳理结果，下面按照节点和关系分别进行数据抽取\n",
    "\n",
    "实体(节点)方面\n",
    "1. 省份\n",
    "2. 城市\n",
    "3. 公司(上市公司 \\ 基金管理公司 \\ 基金托管公司)\n",
    "4. 人（上市公司管理层）\n",
    "5. 基金\n",
    "6. 行业\n",
    "\n",
    "关系方面\n",
    "1. 城市 - [IN_PROVINCE] - > 省份 \n",
    "2. 公司(上市公司\\基金管理公司\\ 基金托管公司) - [IN_CITY] - > 城市\n",
    "4. 公司(上市公司) - [HAS_MANGER] - > 人（上市公司管理层）  [6类]\n",
    "5. 公司(上市公司) - [IN_INDUSTRY] - > 行业\n",
    "6. 基金  - [HAS_MANAGEMNET] - > 公司(基金管理公司)\n",
    "6. 基金  - [HAS_CUSTODIAN] - > 公司(基金托管公司)\n",
    "7. 基金  - [IN_PORTFOLIO] ->  公司(上市公司)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:41.950244Z",
     "start_time": "2020-01-28T04:42:41.942240Z"
    }
   },
   "outputs": [],
   "source": [
    "def generate_input_files(data,dicts,types,output_path='../data/output/'):\n",
    "    cols =list(dicts.keys())\n",
    "    temp = data[cols]\n",
    "    temp.drop_duplicates(cols,inplace=True)\n",
    "\n",
    "    print('save header {}..')\n",
    "    columns = ','.join(dicts.values())\n",
    "    with open(output_path+'{}-header.csv'.format(types),'w') as f:\n",
    "        f.write(columns)\n",
    "    \n",
    "    print('save_data ...\\n',output_path+'{}.csv'.format(types))\n",
    "    temp.to_csv(output_path+'{}.csv'.format(types),index=False,header=False)\n",
    "    print(temp.shape)\n",
    "    return temp\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "output_path = '../data/output/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_path = '../data/'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "DATE = ''"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "company_lsit_name = f'company_lsit_all{DATE}.csv'\n",
    "company_detail_name =  f'company_detail_all{DATE}.csv'\n",
    "stk_managers_name = f'stk_managers{DATE}.csv'\n",
    "fund_basic_name = f'fund_basic{DATE}.csv'\n",
    "fund_company_name = f'fund_company{DATE}.csv'\n",
    "fund_portfolio_name = f'fund_portfolio{DATE}.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:43.105647Z",
     "start_time": "2020-01-28T04:42:43.100643Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts ={}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-26T14:58:19.217230Z",
     "start_time": "2020-01-26T14:58:19.208235Z"
    }
   },
   "source": [
    "## 1.公司\n",
    "合并 处理 基金管理人\\ 基金托管人\\ 上市公司\\"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.1 上市公司"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:44.265247Z",
     "start_time": "2020-01-28T04:42:44.234210Z"
    }
   },
   "outputs": [],
   "source": [
    "usecols = ['ts_code','symbol','name','industry','fullname','market','exchange','list_status',\n",
    "           'list_date','delist_date']\n",
    "\n",
    "df_company_lsit_all =  pd.read_csv(data_path + company_lsit_name,dtype={\"symbol\":str},usecols=usecols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:44.458220Z",
     "start_time": "2020-01-28T04:42:44.440212Z"
    }
   },
   "outputs": [],
   "source": [
    "df_company_lsit_all['list_date'] = df_company_lsit_all['list_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )\n",
    "df_company_lsit_all['delist_date'] = df_company_lsit_all['delist_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )\n",
    "\n",
    "df_company_lsit_all['label_listed_company']='LISTED_COMPANY'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_company_lsit_all['symbol'] = df_company_lsit_all['ts_code'].apply(lambda x: x.split('.')[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ts_code</th>\n",
       "      <th>symbol</th>\n",
       "      <th>name</th>\n",
       "      <th>industry</th>\n",
       "      <th>fullname</th>\n",
       "      <th>market</th>\n",
       "      <th>exchange</th>\n",
       "      <th>list_status</th>\n",
       "      <th>list_date</th>\n",
       "      <th>delist_date</th>\n",
       "      <th>label_listed_company</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000001.SZ</td>\n",
       "      <td>000001</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>银行</td>\n",
       "      <td>平安银行股份有限公司</td>\n",
       "      <td>主板</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>L</td>\n",
       "      <td>19910403</td>\n",
       "      <td>-1</td>\n",
       "      <td>LISTED_COMPANY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000002.SZ</td>\n",
       "      <td>000002</td>\n",
       "      <td>万科A</td>\n",
       "      <td>全国地产</td>\n",
       "      <td>万科企业股份有限公司</td>\n",
       "      <td>主板</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>L</td>\n",
       "      <td>19910129</td>\n",
       "      <td>-1</td>\n",
       "      <td>LISTED_COMPANY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000004.SZ</td>\n",
       "      <td>000004</td>\n",
       "      <td>国农科技</td>\n",
       "      <td>生物制药</td>\n",
       "      <td>深圳中国农大科技股份有限公司</td>\n",
       "      <td>主板</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>L</td>\n",
       "      <td>19910114</td>\n",
       "      <td>-1</td>\n",
       "      <td>LISTED_COMPANY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000005.SZ</td>\n",
       "      <td>000005</td>\n",
       "      <td>世纪星源</td>\n",
       "      <td>环境保护</td>\n",
       "      <td>深圳世纪星源股份有限公司</td>\n",
       "      <td>主板</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>L</td>\n",
       "      <td>19901210</td>\n",
       "      <td>-1</td>\n",
       "      <td>LISTED_COMPANY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000006.SZ</td>\n",
       "      <td>000006</td>\n",
       "      <td>深振业A</td>\n",
       "      <td>区域地产</td>\n",
       "      <td>深圳市振业(集团)股份有限公司</td>\n",
       "      <td>主板</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>L</td>\n",
       "      <td>19920427</td>\n",
       "      <td>-1</td>\n",
       "      <td>LISTED_COMPANY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ts_code  symbol  name industry         fullname market exchange  \\\n",
       "0  000001.SZ  000001  平安银行       银行       平安银行股份有限公司     主板     SZSE   \n",
       "1  000002.SZ  000002   万科A     全国地产       万科企业股份有限公司     主板     SZSE   \n",
       "2  000004.SZ  000004  国农科技     生物制药   深圳中国农大科技股份有限公司     主板     SZSE   \n",
       "3  000005.SZ  000005  世纪星源     环境保护     深圳世纪星源股份有限公司     主板     SZSE   \n",
       "4  000006.SZ  000006  深振业A     区域地产  深圳市振业(集团)股份有限公司     主板     SZSE   \n",
       "\n",
       "  list_status  list_date  delist_date label_listed_company  \n",
       "0           L   19910403           -1       LISTED_COMPANY  \n",
       "1           L   19910129           -1       LISTED_COMPANY  \n",
       "2           L   19910114           -1       LISTED_COMPANY  \n",
       "3           L   19901210           -1       LISTED_COMPANY  \n",
       "4           L   19920427           -1       LISTED_COMPANY  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_company_lsit_all.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.2  基金管理公司"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1.2.1 fund_company  中 基金管理人"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:45.596079Z",
     "start_time": "2020-01-28T04:42:45.444078Z"
    }
   },
   "outputs": [],
   "source": [
    "# 基金管理人数据\n",
    "usecols = ['name','shortname','province','city','address','office','website',\n",
    "            'setup_date','end_date','main_business','org_code','credit_code']\n",
    "df_fund_company = pd.read_csv(data_path + fund_company_name,usecols=usecols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:45.808078Z",
     "start_time": "2020-01-28T04:42:45.768093Z"
    }
   },
   "outputs": [],
   "source": [
    "df_fund_company['setup_date'] = df_fund_company['setup_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )\n",
    "df_fund_company['end_date'] = df_fund_company['end_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:46.531733Z",
     "start_time": "2020-01-28T04:42:46.453650Z"
    }
   },
   "outputs": [],
   "source": [
    "df_fund_company = df_fund_company.rename(columns ={'name':'fullname','shortname':'name'}).drop_duplicates()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "fund_company 和 fund_basic 两张数据表中，有下面五个银行、一个券商、一个资管 名称公司简称**没有对齐**，\n",
    "\n",
    "这里我们手工对其下：\n",
    "处理 几个 ['中国民生银行', '中国光大银行', '中国工商银行', '中国建设银行', '中国农业银行'] 带有 \"中国\" 的公司"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:45.122649Z",
     "start_time": "2020-01-28T04:42:45.118648Z"
    }
   },
   "outputs": [],
   "source": [
    "comp_simplify_dict = {\n",
    "    '中国民生银行':'民生银行',\n",
    "    '中国光大银行':'光大银行', \n",
    "    '中国工商银行':'工商银行', \n",
    "    '中国建设银行':'建设银行',\n",
    "    '中国农业银行':'农业银行',\n",
    "    '中银国际证券':'中银证券',\n",
    "    '财通证券资管':'财通资管'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:46.684650Z",
     "start_time": "2020-01-28T04:42:46.667650Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df_fund_company['name'] = df_fund_company['name'].apply(lambda x : comp_simplify_dict[x] if x in comp_simplify_dict.keys() else x )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>fullname</th>\n",
       "      <td>北京广能投资基金管理有限公司</td>\n",
       "      <td>宏源证券股份有限公司</td>\n",
       "      <td>国元证券股份有限公司</td>\n",
       "      <td>广发证券股份有限公司</td>\n",
       "      <td>长江证券股份有限公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>name</th>\n",
       "      <td>广能基金</td>\n",
       "      <td>宏源证券</td>\n",
       "      <td>国元证券</td>\n",
       "      <td>广发证券</td>\n",
       "      <td>长江证券</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>province</th>\n",
       "      <td>北京</td>\n",
       "      <td>新疆</td>\n",
       "      <td>安徽</td>\n",
       "      <td>广东</td>\n",
       "      <td>湖北</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <td>北京市</td>\n",
       "      <td>乌鲁木齐市</td>\n",
       "      <td>合肥市</td>\n",
       "      <td>广州市</td>\n",
       "      <td>武汉市</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>address</th>\n",
       "      <td>北京市朝阳区北四环中路27号院5号楼2712-2715A</td>\n",
       "      <td>新疆维吾尔自治区乌鲁木齐市文艺路233号宏源大厦</td>\n",
       "      <td>安徽省合肥市梅山路18号</td>\n",
       "      <td>广东省广州市黄埔区中新广州知识城腾飞一街2号618室</td>\n",
       "      <td>湖北省武汉市江汉区新华路特8号</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>office</th>\n",
       "      <td>北京市朝阳区北四环中路27号院5号楼2712-2715A</td>\n",
       "      <td>新疆维吾尔自治区乌鲁木齐市文艺路233号宏源大厦</td>\n",
       "      <td>安徽省合肥市梅山路18号</td>\n",
       "      <td>广东省广州市天河区天河北路183-187号大都会广场40楼5楼,7楼,8楼,18楼,19楼,...</td>\n",
       "      <td>湖北省武汉市江汉区新华路特8号</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>website</th>\n",
       "      <td>www.gnfund.cn</td>\n",
       "      <td>www.hysec.com</td>\n",
       "      <td>www.gyzq.com.cn</td>\n",
       "      <td>www.gf.com.cn</td>\n",
       "      <td>www.cjsc.com</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>setup_date</th>\n",
       "      <td>20111031</td>\n",
       "      <td>19930525</td>\n",
       "      <td>19970606</td>\n",
       "      <td>19940121</td>\n",
       "      <td>19970724</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>end_date</th>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "      <td>-1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>main_business</th>\n",
       "      <td>NaN</td>\n",
       "      <td>主要业务:代理买卖证券.</td>\n",
       "      <td>主营业务:经纪业务,自营投资业务,投行业务,资产管理业务,基金管理业务,期货业务,境外业务国...</td>\n",
       "      <td>主营业务:证券经纪</td>\n",
       "      <td>主营业务:证券经纪</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>org_code</th>\n",
       "      <td>584419680</td>\n",
       "      <td>228593068</td>\n",
       "      <td>731686376</td>\n",
       "      <td>126335439</td>\n",
       "      <td>700821272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>credit_code</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>91340000731686376P</td>\n",
       "      <td>91440000126335439C</td>\n",
       "      <td>91420000700821272A</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                          0                         1  \\\n",
       "fullname                     北京广能投资基金管理有限公司                宏源证券股份有限公司   \n",
       "name                                   广能基金                      宏源证券   \n",
       "province                                 北京                        新疆   \n",
       "city                                    北京市                     乌鲁木齐市   \n",
       "address        北京市朝阳区北四环中路27号院5号楼2712-2715A  新疆维吾尔自治区乌鲁木齐市文艺路233号宏源大厦   \n",
       "office         北京市朝阳区北四环中路27号院5号楼2712-2715A  新疆维吾尔自治区乌鲁木齐市文艺路233号宏源大厦   \n",
       "website                       www.gnfund.cn             www.hysec.com   \n",
       "setup_date                         20111031                  19930525   \n",
       "end_date                                 -1                        -1   \n",
       "main_business                           NaN              主要业务:代理买卖证券.   \n",
       "org_code                          584419680                 228593068   \n",
       "credit_code                             NaN                       NaN   \n",
       "\n",
       "                                                               2  \\\n",
       "fullname                                              国元证券股份有限公司   \n",
       "name                                                        国元证券   \n",
       "province                                                      安徽   \n",
       "city                                                         合肥市   \n",
       "address                                             安徽省合肥市梅山路18号   \n",
       "office                                              安徽省合肥市梅山路18号   \n",
       "website                                          www.gyzq.com.cn   \n",
       "setup_date                                              19970606   \n",
       "end_date                                                      -1   \n",
       "main_business  主营业务:经纪业务,自营投资业务,投行业务,资产管理业务,基金管理业务,期货业务,境外业务国...   \n",
       "org_code                                               731686376   \n",
       "credit_code                                   91340000731686376P   \n",
       "\n",
       "                                                               3  \\\n",
       "fullname                                              广发证券股份有限公司   \n",
       "name                                                        广发证券   \n",
       "province                                                      广东   \n",
       "city                                                         广州市   \n",
       "address                               广东省广州市黄埔区中新广州知识城腾飞一街2号618室   \n",
       "office         广东省广州市天河区天河北路183-187号大都会广场40楼5楼,7楼,8楼,18楼,19楼,...   \n",
       "website                                            www.gf.com.cn   \n",
       "setup_date                                              19940121   \n",
       "end_date                                                      -1   \n",
       "main_business                                          主营业务:证券经纪   \n",
       "org_code                                               126335439   \n",
       "credit_code                                   91440000126335439C   \n",
       "\n",
       "                                4  \n",
       "fullname               长江证券股份有限公司  \n",
       "name                         长江证券  \n",
       "province                       湖北  \n",
       "city                          武汉市  \n",
       "address           湖北省武汉市江汉区新华路特8号  \n",
       "office            湖北省武汉市江汉区新华路特8号  \n",
       "website              www.cjsc.com  \n",
       "setup_date               19970724  \n",
       "end_date                       -1  \n",
       "main_business           主营业务:证券经纪  \n",
       "org_code                700821272  \n",
       "credit_code    91420000700821272A  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_fund_company.head().T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 1.2.2 fund_basic 中基金管理人"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:27:57.177267Z",
     "start_time": "2020-01-28T05:27:57.086178Z"
    }
   },
   "outputs": [],
   "source": [
    "# 基金数据 \n",
    "df_fund_basic = pd.read_csv(data_path + fund_basic_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_fund_basic['management'] = df_fund_basic['management'].apply(lambda x : comp_simplify_dict[x] if x in comp_simplify_dict.keys() else x )\n",
    "df_fund_basic['custodian'] = df_fund_basic['custodian'].apply(lambda x : comp_simplify_dict[x] if x in comp_simplify_dict.keys() else x )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "#  df_fund_basic 中 管理人 management\n",
    "node_fund_management = df_fund_basic.loc[df_fund_basic.management.notnull(),['management']]\n",
    "node_fund_management = node_fund_management.rename(columns = {'management':'name'})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 1.2.3 合并数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_fund_company = pd.merge(df_fund_company,node_fund_management,on=['name'],how='outer').drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fullname</th>\n",
       "      <th>name</th>\n",
       "      <th>province</th>\n",
       "      <th>city</th>\n",
       "      <th>address</th>\n",
       "      <th>office</th>\n",
       "      <th>website</th>\n",
       "      <th>setup_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>main_business</th>\n",
       "      <th>org_code</th>\n",
       "      <th>credit_code</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>25788</th>\n",
       "      <td>NaN</td>\n",
       "      <td>平安基金</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25952</th>\n",
       "      <td>NaN</td>\n",
       "      <td>东证资管</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26008</th>\n",
       "      <td>NaN</td>\n",
       "      <td>华泰证券资管</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26027</th>\n",
       "      <td>NaN</td>\n",
       "      <td>惠升基金</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26031</th>\n",
       "      <td>NaN</td>\n",
       "      <td>同泰基金</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26039</th>\n",
       "      <td>NaN</td>\n",
       "      <td>中泰资管</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26050</th>\n",
       "      <td>NaN</td>\n",
       "      <td>西藏东财基金</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26054</th>\n",
       "      <td>NaN</td>\n",
       "      <td>朱雀基金</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26058</th>\n",
       "      <td>NaN</td>\n",
       "      <td>淳厚基金</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26062</th>\n",
       "      <td>NaN</td>\n",
       "      <td>博远基金</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26064</th>\n",
       "      <td>NaN</td>\n",
       "      <td>山西证券</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26078</th>\n",
       "      <td>NaN</td>\n",
       "      <td>睿远基金</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26080</th>\n",
       "      <td>NaN</td>\n",
       "      <td>华融基金</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      fullname    name province city address office website  setup_date  \\\n",
       "25788      NaN    平安基金      NaN  NaN     NaN    NaN     NaN         NaN   \n",
       "25952      NaN    东证资管      NaN  NaN     NaN    NaN     NaN         NaN   \n",
       "26008      NaN  华泰证券资管      NaN  NaN     NaN    NaN     NaN         NaN   \n",
       "26027      NaN    惠升基金      NaN  NaN     NaN    NaN     NaN         NaN   \n",
       "26031      NaN    同泰基金      NaN  NaN     NaN    NaN     NaN         NaN   \n",
       "26039      NaN    中泰资管      NaN  NaN     NaN    NaN     NaN         NaN   \n",
       "26050      NaN  西藏东财基金      NaN  NaN     NaN    NaN     NaN         NaN   \n",
       "26054      NaN    朱雀基金      NaN  NaN     NaN    NaN     NaN         NaN   \n",
       "26058      NaN    淳厚基金      NaN  NaN     NaN    NaN     NaN         NaN   \n",
       "26062      NaN    博远基金      NaN  NaN     NaN    NaN     NaN         NaN   \n",
       "26064      NaN    山西证券      NaN  NaN     NaN    NaN     NaN         NaN   \n",
       "26078      NaN    睿远基金      NaN  NaN     NaN    NaN     NaN         NaN   \n",
       "26080      NaN    华融基金      NaN  NaN     NaN    NaN     NaN         NaN   \n",
       "\n",
       "       end_date main_business org_code credit_code  \n",
       "25788       NaN           NaN      NaN         NaN  \n",
       "25952       NaN           NaN      NaN         NaN  \n",
       "26008       NaN           NaN      NaN         NaN  \n",
       "26027       NaN           NaN      NaN         NaN  \n",
       "26031       NaN           NaN      NaN         NaN  \n",
       "26039       NaN           NaN      NaN         NaN  \n",
       "26050       NaN           NaN      NaN         NaN  \n",
       "26054       NaN           NaN      NaN         NaN  \n",
       "26058       NaN           NaN      NaN         NaN  \n",
       "26062       NaN           NaN      NaN         NaN  \n",
       "26064       NaN           NaN      NaN         NaN  \n",
       "26078       NaN           NaN      NaN         NaN  \n",
       "26080       NaN           NaN      NaN         NaN  "
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_fund_company[df_fund_company['fullname'].isnull()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "fund_basic 中基金管理人（management）中有 12 个公司 没有 在 资管数据表 fund_company 中出现。 需要额外补全，其全称"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 基金管理人补齐全称\n",
    "fund_manage_fullname_dict = {\n",
    "    '东证资管':'东方证券资产管理有限公司',\n",
    "    '中泰资管':'中泰证券（上海）资产管理有限公司',\n",
    "    '华泰证券资管':'华华泰证券资产管理公司',\n",
    "    '华融基金':'华融基金管理有限公司',\n",
    "    '博远基金':'博远基金管理有限公司',\n",
    "    '同泰基金':'同泰基金管理有限公司',\n",
    "    '平安基金':'平安基金管理有限公司',\n",
    "    '惠升基金':'惠升基金管理有限公司',\n",
    "    '朱雀基金':'朱雀基金管理有限公司',\n",
    "    '淳厚基金':'淳厚基金管理有限公司',\n",
    "    '睿远基金':'睿远基金管理有限公司',\n",
    "    '西藏东财基金':'西藏东财基金管理有限公司',\n",
    "    '山西证券':'山西证券股份有限公司',\n",
    "    '恒丰银行':'恒丰银行股份有限公司',\n",
    "    '广发银行':'广发银行股份有限公司'\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_fund_company['fullname'] = df_fund_company.apply(lambda x:fund_manage_fullname_dict.get(x['name']) if pd.isnull(x['fullname']) else x['fullname'],axis=1 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:46.203523Z",
     "start_time": "2020-01-28T04:42:46.199519Z"
    }
   },
   "outputs": [],
   "source": [
    "df_fund_company['label_manager'] = 'FUND_MANAGER'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fullname</th>\n",
       "      <th>name</th>\n",
       "      <th>province</th>\n",
       "      <th>city</th>\n",
       "      <th>address</th>\n",
       "      <th>office</th>\n",
       "      <th>website</th>\n",
       "      <th>setup_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>main_business</th>\n",
       "      <th>org_code</th>\n",
       "      <th>credit_code</th>\n",
       "      <th>label_manager</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>北京广能投资基金管理有限公司</td>\n",
       "      <td>广能基金</td>\n",
       "      <td>北京</td>\n",
       "      <td>北京市</td>\n",
       "      <td>北京市朝阳区北四环中路27号院5号楼2712-2715A</td>\n",
       "      <td>北京市朝阳区北四环中路27号院5号楼2712-2715A</td>\n",
       "      <td>www.gnfund.cn</td>\n",
       "      <td>20111031.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>584419680</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FUND_MANAGER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>宏源证券股份有限公司</td>\n",
       "      <td>宏源证券</td>\n",
       "      <td>新疆</td>\n",
       "      <td>乌鲁木齐市</td>\n",
       "      <td>新疆维吾尔自治区乌鲁木齐市文艺路233号宏源大厦</td>\n",
       "      <td>新疆维吾尔自治区乌鲁木齐市文艺路233号宏源大厦</td>\n",
       "      <td>www.hysec.com</td>\n",
       "      <td>19930525.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>主要业务:代理买卖证券.</td>\n",
       "      <td>228593068</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FUND_MANAGER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>国元证券股份有限公司</td>\n",
       "      <td>国元证券</td>\n",
       "      <td>安徽</td>\n",
       "      <td>合肥市</td>\n",
       "      <td>安徽省合肥市梅山路18号</td>\n",
       "      <td>安徽省合肥市梅山路18号</td>\n",
       "      <td>www.gyzq.com.cn</td>\n",
       "      <td>19970606.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>主营业务:经纪业务,自营投资业务,投行业务,资产管理业务,基金管理业务,期货业务,境外业务国...</td>\n",
       "      <td>731686376</td>\n",
       "      <td>91340000731686376P</td>\n",
       "      <td>FUND_MANAGER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>广发证券股份有限公司</td>\n",
       "      <td>广发证券</td>\n",
       "      <td>广东</td>\n",
       "      <td>广州市</td>\n",
       "      <td>广东省广州市黄埔区中新广州知识城腾飞一街2号618室</td>\n",
       "      <td>广东省广州市天河区天河北路183-187号大都会广场40楼5楼,7楼,8楼,18楼,19楼,...</td>\n",
       "      <td>www.gf.com.cn</td>\n",
       "      <td>19940121.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>主营业务:证券经纪</td>\n",
       "      <td>126335439</td>\n",
       "      <td>91440000126335439C</td>\n",
       "      <td>FUND_MANAGER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>长江证券股份有限公司</td>\n",
       "      <td>长江证券</td>\n",
       "      <td>湖北</td>\n",
       "      <td>武汉市</td>\n",
       "      <td>湖北省武汉市江汉区新华路特8号</td>\n",
       "      <td>湖北省武汉市江汉区新华路特8号</td>\n",
       "      <td>www.cjsc.com</td>\n",
       "      <td>19970724.0</td>\n",
       "      <td>-1.0</td>\n",
       "      <td>主营业务:证券经纪</td>\n",
       "      <td>700821272</td>\n",
       "      <td>91420000700821272A</td>\n",
       "      <td>FUND_MANAGER</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         fullname  name province   city                       address  \\\n",
       "0  北京广能投资基金管理有限公司  广能基金       北京    北京市  北京市朝阳区北四环中路27号院5号楼2712-2715A   \n",
       "1      宏源证券股份有限公司  宏源证券       新疆  乌鲁木齐市      新疆维吾尔自治区乌鲁木齐市文艺路233号宏源大厦   \n",
       "2      国元证券股份有限公司  国元证券       安徽    合肥市                  安徽省合肥市梅山路18号   \n",
       "3      广发证券股份有限公司  广发证券       广东    广州市    广东省广州市黄埔区中新广州知识城腾飞一街2号618室   \n",
       "4      长江证券股份有限公司  长江证券       湖北    武汉市               湖北省武汉市江汉区新华路特8号   \n",
       "\n",
       "                                              office          website  \\\n",
       "0                       北京市朝阳区北四环中路27号院5号楼2712-2715A    www.gnfund.cn   \n",
       "1                           新疆维吾尔自治区乌鲁木齐市文艺路233号宏源大厦    www.hysec.com   \n",
       "2                                       安徽省合肥市梅山路18号  www.gyzq.com.cn   \n",
       "3  广东省广州市天河区天河北路183-187号大都会广场40楼5楼,7楼,8楼,18楼,19楼,...    www.gf.com.cn   \n",
       "4                                    湖北省武汉市江汉区新华路特8号     www.cjsc.com   \n",
       "\n",
       "   setup_date  end_date                                      main_business  \\\n",
       "0  20111031.0      -1.0                                                NaN   \n",
       "1  19930525.0      -1.0                                       主要业务:代理买卖证券.   \n",
       "2  19970606.0      -1.0  主营业务:经纪业务,自营投资业务,投行业务,资产管理业务,基金管理业务,期货业务,境外业务国...   \n",
       "3  19940121.0      -1.0                                          主营业务:证券经纪   \n",
       "4  19970724.0      -1.0                                          主营业务:证券经纪   \n",
       "\n",
       "    org_code         credit_code label_manager  \n",
       "0  584419680                 NaN  FUND_MANAGER  \n",
       "1  228593068                 NaN  FUND_MANAGER  \n",
       "2  731686376  91340000731686376P  FUND_MANAGER  \n",
       "3  126335439  91440000126335439C  FUND_MANAGER  \n",
       "4  700821272  91420000700821272A  FUND_MANAGER  "
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_fund_company.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.3 基金托管公司"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "托管人中存在下面8个没有在大陆上市的公司。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- '中泰证券': 未上市\n",
    "- '中金公司': HK中金公司(03908)\n",
    "- '包商银行': 未上市\n",
    "- '广发银行': 未上市\n",
    "- '广州农商银行': 广州农商银行 01551.HK\n",
    "- '徽商银行':  徽商银行03698.HK\n",
    "- '恒丰银行': 未上市\n",
    "- '渤海银行': 未上市"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:47.906519Z",
     "start_time": "2020-01-28T04:42:47.892520Z"
    }
   },
   "outputs": [],
   "source": [
    "node_fund_custodian = df_fund_basic.loc[df_fund_basic.custodian.notnull(),['custodian']].drop_duplicates()\n",
    "node_fund_custodian = node_fund_custodian.rename(columns = {'custodian':'name'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:42:49.114581Z",
     "start_time": "2020-01-28T04:42:49.109585Z"
    }
   },
   "outputs": [],
   "source": [
    "node_fund_custodian['label_custodian'] = 'FUND_CUSTODIAN'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(40, 2)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "node_fund_custodian.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.4 合并数据·"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:43:12.979068Z",
     "start_time": "2020-01-28T04:43:12.854053Z"
    }
   },
   "outputs": [],
   "source": [
    "node_companies = pd.merge(df_company_lsit_all,df_fund_company,on=['fullname','name'],how='outer').drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:43:13.517525Z",
     "start_time": "2020-01-28T04:43:13.406450Z"
    }
   },
   "outputs": [],
   "source": [
    "node_companies = pd.merge(node_companies,node_fund_custodian,on=['name'],how='outer').drop_duplicates()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "18971    广发银行\n",
       "18972    恒丰银行\n",
       "Name: name, dtype: object"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "node_companies[node_companies.fullname.isnull()].name"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-27T12:27:30.821355Z",
     "start_time": "2020-01-27T12:27:30.808359Z"
    }
   },
   "source": [
    " 有**2** 家公司没有拼上,分别是 `恒丰银行`,`广发银行`.\n",
    " 通过网查,将两个公司的全名补上"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:43:28.916110Z",
     "start_time": "2020-01-28T04:43:28.897113Z"
    }
   },
   "outputs": [],
   "source": [
    "node_companies.loc[node_companies.name == '恒丰银行','fullname'] = '恒丰银行股份有限公司'\n",
    "node_companies.loc[node_companies.name == '广发银行','fullname'] = '广发银行股份有限公司'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:43:35.460694Z",
     "start_time": "2020-01-28T04:43:35.432695Z"
    },
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ts_code</th>\n",
       "      <th>symbol</th>\n",
       "      <th>name</th>\n",
       "      <th>industry</th>\n",
       "      <th>fullname</th>\n",
       "      <th>market</th>\n",
       "      <th>exchange</th>\n",
       "      <th>list_status</th>\n",
       "      <th>list_date</th>\n",
       "      <th>delist_date</th>\n",
       "      <th>...</th>\n",
       "      <th>address</th>\n",
       "      <th>office</th>\n",
       "      <th>website</th>\n",
       "      <th>setup_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>main_business</th>\n",
       "      <th>org_code</th>\n",
       "      <th>credit_code</th>\n",
       "      <th>label_manager</th>\n",
       "      <th>label_custodian</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 23 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [ts_code, symbol, name, industry, fullname, market, exchange, list_status, list_date, delist_date, label_listed_company, province, city, address, office, website, setup_date, end_date, main_business, org_code, credit_code, label_manager, label_custodian]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 23 columns]"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "node_companies[node_companies.fullname.isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "缺失的资管公司数： 0\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "set()"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print('缺失的资管公司数：',len(set(df_fund_basic.management) - set(node_companies.name)))\n",
    "\n",
    "set(df_fund_basic.management) - set(node_companies.name)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.5  处理标签"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:47:10.092333Z",
     "start_time": "2020-01-28T04:47:10.080338Z"
    }
   },
   "outputs": [],
   "source": [
    "node_companies['label_listed_company'] = node_companies['label_listed_company'].fillna('')\n",
    "node_companies['label_manager'] = node_companies['label_manager'].fillna('')\n",
    "node_companies['label_custodian'] = node_companies['label_custodian'].fillna('')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:49:55.703223Z",
     "start_time": "2020-01-28T04:49:55.698254Z"
    }
   },
   "outputs": [],
   "source": [
    "def join_label(ls):\n",
    "    return 'COMPANY;'+ ';'.join([c for c in ls  if c !=''])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:50:07.071753Z",
     "start_time": "2020-01-28T04:50:06.340676Z"
    }
   },
   "outputs": [],
   "source": [
    "node_companies['label'] = node_companies.apply(lambda x: join_label([x['label_listed_company'],x['label_manager'],x['label_custodian']]),axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [],
   "source": [
    "# node_companies.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:50:59.563656Z",
     "start_time": "2020-01-28T04:50:59.551658Z"
    }
   },
   "outputs": [],
   "source": [
    "# 基于 上市公司信息 company_lsit_all\n",
    "all_dicts['node_companies'] = {  'fullname':'company:ID(company)', \n",
    "                                'name':'short_name', # 股票名称\n",
    "                                'symbol':'share_code', # 股票全称\n",
    "                                'market':'market', # 市场类型 （主板/中小板/创业板/科创板）\n",
    "                                'exchange':'exchange', # 交易所代码\n",
    "                                'list_status':'list_status', # 上市状态： L上市 D退市 P暂停上市\n",
    "                                'list_date':'list_date:float', # 上市日期\n",
    "                                'delist_date':'delist_date:float', # 退市日期\n",
    "                                'setup_date':'setup_date',   # 成立日期\n",
    "                                'label':':LABEL'\n",
    "                           }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:51:01.010095Z",
     "start_time": "2020-01-28T04:51:00.707099Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  after removing the cwd from sys.path.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data/output/node_companies.csv\n",
      "(18973, 10)\n"
     ]
    }
   ],
   "source": [
    "types = 'node_companies'\n",
    "temp = generate_input_files(node_companies,dicts=all_dicts[types] ,types = types,output_path = output_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 节点：行业"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:51:29.717202Z",
     "start_time": "2020-01-28T04:51:29.713197Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts['node_industries'] = {'industry':'industry:ID(industry)', # 所属行业 \n",
    "                                'label':':LABEL'\n",
    "                               }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:51:35.509913Z",
     "start_time": "2020-01-28T04:51:35.501922Z"
    }
   },
   "outputs": [],
   "source": [
    "df_industry = pd.DataFrame({'industry':[c for c in df_company_lsit_all.industry.unique() if not pd.isnull(c)]})\n",
    "df_industry['label']='INDUSTRY'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>industry</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>银行</td>\n",
       "      <td>INDUSTRY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>全国地产</td>\n",
       "      <td>INDUSTRY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>生物制药</td>\n",
       "      <td>INDUSTRY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>环境保护</td>\n",
       "      <td>INDUSTRY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>区域地产</td>\n",
       "      <td>INDUSTRY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  industry     label\n",
       "0       银行  INDUSTRY\n",
       "1     全国地产  INDUSTRY\n",
       "2     生物制药  INDUSTRY\n",
       "3     环境保护  INDUSTRY\n",
       "4     区域地产  INDUSTRY"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_industry.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:51:40.688382Z",
     "start_time": "2020-01-28T04:51:40.618403Z"
    },
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data/output/node_industries.csv\n",
      "(110, 2)\n"
     ]
    }
   ],
   "source": [
    "types = 'node_industries'\n",
    "temp = generate_input_files(df_industry,dicts=all_dicts[types],types = types,output_path = output_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 关系：股票-->行业"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:52:50.947312Z",
     "start_time": "2020-01-28T04:52:50.942311Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts['rel_share_in_industry'] = {'fullname':':START_ID(company)', # 股票代码\n",
    "                                      'industry':':END_ID(industry)', # 所属行业\n",
    "                                      'type':':TYPE'\n",
    "                                      }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:52:51.524828Z",
     "start_time": "2020-01-28T04:52:51.467831Z"
    }
   },
   "outputs": [],
   "source": [
    "rel_share_in_industry = df_company_lsit_all[['industry','fullname']]\n",
    "\n",
    "rel_share_in_industry = rel_share_in_industry[rel_share_in_industry['fullname'].notnull()&rel_share_in_industry['industry'].notnull()]\n",
    "rel_share_in_industry['type']='IN_INDUSTRY'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:52:51.989859Z",
     "start_time": "2020-01-28T04:52:51.921864Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data/output/rel_share_in_industry.csv\n",
      "(3744, 3)\n"
     ]
    }
   ],
   "source": [
    "types = 'rel_share_in_industry'\n",
    "temp = generate_input_files(rel_share_in_industry,dicts=all_dicts[types],types = types, output_path = output_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:52:56.040141Z",
     "start_time": "2020-01-28T04:52:56.020139Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ts_code</th>\n",
       "      <th>symbol</th>\n",
       "      <th>name</th>\n",
       "      <th>industry</th>\n",
       "      <th>fullname</th>\n",
       "      <th>market</th>\n",
       "      <th>exchange</th>\n",
       "      <th>list_status</th>\n",
       "      <th>list_date</th>\n",
       "      <th>delist_date</th>\n",
       "      <th>label_listed_company</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>000001.SZ</td>\n",
       "      <td>000001</td>\n",
       "      <td>平安银行</td>\n",
       "      <td>银行</td>\n",
       "      <td>平安银行股份有限公司</td>\n",
       "      <td>主板</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>L</td>\n",
       "      <td>19910403</td>\n",
       "      <td>-1</td>\n",
       "      <td>LISTED_COMPANY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>000002.SZ</td>\n",
       "      <td>000002</td>\n",
       "      <td>万科A</td>\n",
       "      <td>全国地产</td>\n",
       "      <td>万科企业股份有限公司</td>\n",
       "      <td>主板</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>L</td>\n",
       "      <td>19910129</td>\n",
       "      <td>-1</td>\n",
       "      <td>LISTED_COMPANY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>000004.SZ</td>\n",
       "      <td>000004</td>\n",
       "      <td>国农科技</td>\n",
       "      <td>生物制药</td>\n",
       "      <td>深圳中国农大科技股份有限公司</td>\n",
       "      <td>主板</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>L</td>\n",
       "      <td>19910114</td>\n",
       "      <td>-1</td>\n",
       "      <td>LISTED_COMPANY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>000005.SZ</td>\n",
       "      <td>000005</td>\n",
       "      <td>世纪星源</td>\n",
       "      <td>环境保护</td>\n",
       "      <td>深圳世纪星源股份有限公司</td>\n",
       "      <td>主板</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>L</td>\n",
       "      <td>19901210</td>\n",
       "      <td>-1</td>\n",
       "      <td>LISTED_COMPANY</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>000006.SZ</td>\n",
       "      <td>000006</td>\n",
       "      <td>深振业A</td>\n",
       "      <td>区域地产</td>\n",
       "      <td>深圳市振业(集团)股份有限公司</td>\n",
       "      <td>主板</td>\n",
       "      <td>SZSE</td>\n",
       "      <td>L</td>\n",
       "      <td>19920427</td>\n",
       "      <td>-1</td>\n",
       "      <td>LISTED_COMPANY</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     ts_code  symbol  name industry         fullname market exchange  \\\n",
       "0  000001.SZ  000001  平安银行       银行       平安银行股份有限公司     主板     SZSE   \n",
       "1  000002.SZ  000002   万科A     全国地产       万科企业股份有限公司     主板     SZSE   \n",
       "2  000004.SZ  000004  国农科技     生物制药   深圳中国农大科技股份有限公司     主板     SZSE   \n",
       "3  000005.SZ  000005  世纪星源     环境保护     深圳世纪星源股份有限公司     主板     SZSE   \n",
       "4  000006.SZ  000006  深振业A     区域地产  深圳市振业(集团)股份有限公司     主板     SZSE   \n",
       "\n",
       "  list_status  list_date  delist_date label_listed_company  \n",
       "0           L   19910403           -1       LISTED_COMPANY  \n",
       "1           L   19910129           -1       LISTED_COMPANY  \n",
       "2           L   19910114           -1       LISTED_COMPANY  \n",
       "3           L   19901210           -1       LISTED_COMPANY  \n",
       "4           L   19920427           -1       LISTED_COMPANY  "
      ]
     },
     "execution_count": 55,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_company_lsit_all.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 节点：省份"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-20T05:40:34.648826Z",
     "start_time": "2020-01-20T05:40:34.599823Z"
    }
   },
   "source": [
    "##### 上市公司基本信息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:58:58.703501Z",
     "start_time": "2020-01-28T04:58:58.664464Z"
    }
   },
   "outputs": [],
   "source": [
    "df_company_detail = pd.read_csv(data_path + company_detail_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:59:24.507939Z",
     "start_time": "2020-01-28T04:59:24.499946Z"
    }
   },
   "outputs": [],
   "source": [
    "df_company_detail['symbol'] = df_company_detail['ts_code'].apply(lambda x:x.split('.')[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:54:21.016457Z",
     "start_time": "2020-01-28T04:54:21.011488Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts['node_province'] = {'province':'province:ID(province)', # 所属行业 \n",
    "                                'label':':LABEL'\n",
    "                               }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:54:21.636638Z",
     "start_time": "2020-01-28T04:54:21.584639Z"
    }
   },
   "outputs": [],
   "source": [
    "node_province1 = pd.DataFrame({'province':[c for c in df_company_detail.province.unique() if not pd.isnull(c)]})\n",
    "node_province2 = pd.DataFrame({'province':[c for c in df_fund_company.province.unique() if not pd.isnull(c)]})\n",
    "node_province = pd.concat([node_province1,node_province2]).drop_duplicates()\n",
    "node_province['label']='PROVINCE'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:54:24.191310Z",
     "start_time": "2020-01-28T04:54:24.173306Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data/output/node_province.csv\n",
      "(32, 2)\n"
     ]
    }
   ],
   "source": [
    "types = 'node_province'\n",
    "temp = generate_input_files(node_province,dicts=all_dicts[types],types = types, output_path = output_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 节点：城市"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:54:29.535893Z",
     "start_time": "2020-01-28T04:54:29.531896Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts['node_city'] = {'city':'city:ID(city)', # 所属行业 \n",
    "                          'label':':LABEL'\n",
    "                          }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:54:30.306941Z",
     "start_time": "2020-01-28T04:54:30.250944Z"
    }
   },
   "outputs": [],
   "source": [
    "node_city1 = pd.DataFrame({'city':[c for c in df_company_detail.city.unique() if not pd.isnull(c)]})\n",
    "node_city2 = pd.DataFrame({'city':[c for c in df_fund_company.city.unique() if not pd.isnull(c)]})\n",
    "node_city = pd.concat([node_city1,node_city2]).drop_duplicates()\n",
    "\n",
    "node_city['label']='CITY'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T04:54:31.954057Z",
     "start_time": "2020-01-28T04:54:31.937081Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data/output/node_city.csv\n",
      "(341, 2)\n"
     ]
    }
   ],
   "source": [
    "types = 'node_city'\n",
    "temp = generate_input_files(node_city,dicts=all_dicts[types],types = types,output_path = output_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 关系：公司-->城市"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:03:12.646736Z",
     "start_time": "2020-01-28T05:03:12.560742Z"
    }
   },
   "outputs": [],
   "source": [
    "rel_company_in_city1 = pd.merge(df_company_detail,df_company_lsit_all[['symbol','fullname']],on='symbol',how='left')[['fullname','city']]\n",
    "rel_company_in_city2 = df_fund_company[['fullname','city']]\n",
    "\n",
    "rel_company_in_city = pd.concat([rel_company_in_city1,rel_company_in_city2]).drop_duplicates()\n",
    "\n",
    "rel_company_in_city['type']='IN_CITY'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 66,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:03:13.263279Z",
     "start_time": "2020-01-28T05:03:13.257278Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts['rel_company_in_city'] = {'fullname':':START_ID(company)', # 股票代码\n",
    "                                  'city':':END_ID(city)', # 所属城市\n",
    "                                  'type':':TYPE'\n",
    "                                  }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:03:45.458429Z",
     "start_time": "2020-01-28T05:03:45.447432Z"
    }
   },
   "outputs": [],
   "source": [
    "rel_company_in_city = rel_company_in_city[rel_company_in_city['city'].notnull()&rel_company_in_city['fullname'].notnull()]\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:03:59.046597Z",
     "start_time": "2020-01-28T05:03:58.967630Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data/output/rel_company_in_city.csv\n",
      "(18943, 3)\n"
     ]
    }
   ],
   "source": [
    "types = 'rel_company_in_city'\n",
    "temp = generate_input_files(rel_company_in_city,dicts=all_dicts[types],types = types,output_path = output_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 关系：城市-->省份"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:04:41.238480Z",
     "start_time": "2020-01-28T05:04:41.235479Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts['rel_city_in_province'] = {'city':':START_ID(city)', \n",
    "                                  'province':':END_ID(province)', \n",
    "                                  'type':':TYPE'\n",
    "                                  }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:05:42.129526Z",
     "start_time": "2020-01-28T05:05:42.062523Z"
    }
   },
   "outputs": [],
   "source": [
    "rel_city_in_province1= df_company_detail[['province','city']]\n",
    "rel_city_in_province2= df_fund_company[['province','city']]\n",
    "rel_city_in_province = pd.concat([rel_city_in_province1,rel_city_in_province2]).drop_duplicates()\n",
    "\n",
    "rel_city_in_province = rel_city_in_province[rel_city_in_province['province'].notnull()&rel_city_in_province['city'].notnull()]\n",
    "rel_city_in_province['type']='IN_PROVINCE'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:05:59.191692Z",
     "start_time": "2020-01-28T05:05:59.135694Z"
    },
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data/output/rel_city_in_province.csv\n",
      "(341, 3)\n"
     ]
    }
   ],
   "source": [
    "types = 'rel_city_in_province'\n",
    "\n",
    "temp = generate_input_files(rel_city_in_province,dicts=all_dicts[types],types = types,output_path = output_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 节点：人（上市公司董事高管）"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "对于公司高管，因为缺少人的唯一身份id，为了避免同名的影响，采用 姓名+性别+生日 进行hash 生成唯一标识。\n",
    "\n",
    "做法参考：https://github.com/lemonhu/stock-knowledge-graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:06:09.957381Z",
     "start_time": "2020-01-28T05:06:09.952379Z"
    }
   },
   "outputs": [],
   "source": [
    "import hashlib"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 76,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:06:10.521532Z",
     "start_time": "2020-01-28T05:06:10.517536Z"
    }
   },
   "outputs": [],
   "source": [
    "def get_md5(string):\n",
    "    \"\"\"Get md5 according to the string\n",
    "    \"\"\"\n",
    "    byte_string = string.encode(\"utf-8\")\n",
    "    md5 = hashlib.md5()\n",
    "    md5.update(byte_string)\n",
    "    result = md5.hexdigest()\n",
    "    return result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 77,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:06:12.766051Z",
     "start_time": "2020-01-28T05:06:11.855692Z"
    }
   },
   "outputs": [],
   "source": [
    "df_stk_managers = pd.read_csv(data_path + stk_managers_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:09:48.415427Z",
     "start_time": "2020-01-28T05:09:28.001377Z"
    }
   },
   "outputs": [],
   "source": [
    "df_stk_managers['birthday'] = df_stk_managers['birthday'].apply(lambda x: -1 if pd.isnull(x) else int(x))\n",
    "df_stk_managers['gender'] = df_stk_managers['gender'].apply(lambda x: -1 if x not in ['F','M'] else x)\n",
    "df_stk_managers['hash_cust'] = df_stk_managers.apply(lambda x:get_md5('{}-{}-{}'.format(x['name'],x['gender'],x['birthday'])),axis=1 )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:09:48.423378Z",
     "start_time": "2020-01-28T05:09:48.418374Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts['node_managers'] = {'hash_cust':'manager_id:ID(manager_id)', \n",
    "                              'name':'name', \n",
    "                              'gender':'gender',\n",
    "                              'edu':'edu',\n",
    "                              'national':'national',\n",
    "                              'birthday':'birthday:long', \n",
    "                              'label':':LABEL'\n",
    "                          }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:09:48.489374Z",
     "start_time": "2020-01-28T05:09:48.427376Z"
    }
   },
   "outputs": [],
   "source": [
    "types = 'node_managers'\n",
    "\n",
    "df_stk_managers['label'] = 'MANAGER'\n",
    "node_managers = df_stk_managers[list(all_dicts[types].keys())]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>hash_cust</th>\n",
       "      <th>name</th>\n",
       "      <th>gender</th>\n",
       "      <th>edu</th>\n",
       "      <th>national</th>\n",
       "      <th>birthday</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>6b549e55dc02acb8483d76625790a70c</td>\n",
       "      <td>徐宏</td>\n",
       "      <td>M</td>\n",
       "      <td>本科</td>\n",
       "      <td>中国</td>\n",
       "      <td>1973</td>\n",
       "      <td>MANAGER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>7f260c335bc616344b3de75b4ed212f3</td>\n",
       "      <td>张彧</td>\n",
       "      <td>F</td>\n",
       "      <td>硕士</td>\n",
       "      <td>中国</td>\n",
       "      <td>1972</td>\n",
       "      <td>MANAGER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>126b1cb357ab8b4b777708c91ec930f5</td>\n",
       "      <td>张近东</td>\n",
       "      <td>M</td>\n",
       "      <td>本科</td>\n",
       "      <td>中国</td>\n",
       "      <td>1963</td>\n",
       "      <td>MANAGER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>c1576573eef4f6061b59f1c2491d56f6</td>\n",
       "      <td>肖忠祥</td>\n",
       "      <td>M</td>\n",
       "      <td>本科</td>\n",
       "      <td>中国</td>\n",
       "      <td>1971</td>\n",
       "      <td>MANAGER</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>06a551a6cf1e98790df9f1dbb1419841</td>\n",
       "      <td>汪晓玲</td>\n",
       "      <td>F</td>\n",
       "      <td>本科</td>\n",
       "      <td>中国</td>\n",
       "      <td>1973</td>\n",
       "      <td>MANAGER</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          hash_cust name gender edu national  birthday  \\\n",
       "0  6b549e55dc02acb8483d76625790a70c   徐宏      M  本科       中国      1973   \n",
       "1  7f260c335bc616344b3de75b4ed212f3   张彧      F  硕士       中国      1972   \n",
       "2  126b1cb357ab8b4b777708c91ec930f5  张近东      M  本科       中国      1963   \n",
       "3  c1576573eef4f6061b59f1c2491d56f6  肖忠祥      M  本科       中国      1971   \n",
       "4  06a551a6cf1e98790df9f1dbb1419841  汪晓玲      F  本科       中国      1973   \n",
       "\n",
       "     label  \n",
       "0  MANAGER  \n",
       "1  MANAGER  \n",
       "2  MANAGER  \n",
       "3  MANAGER  \n",
       "4  MANAGER  "
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "node_managers.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:09:50.489376Z",
     "start_time": "2020-01-28T05:09:48.496380Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data/output/node_managers.csv\n",
      "(163613, 7)\n"
     ]
    }
   ],
   "source": [
    "node_managers = node_managers.sort_values(['name','gender','edu','national']).drop_duplicates('hash_cust',keep='first')\n",
    "\n",
    "temp = generate_input_files(node_managers,dicts=all_dicts[types],types = types, output_path = output_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 关系：公司（上市公司）--> 人(董事高管)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "计划将高管关系分为六类"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "|中文| 英文|数量|占比|\n",
    "|--|--|--|--|\n",
    "|董事会成员 |Has_Director|125040|0.278736|\n",
    "|监事|Has_Supervisor|60332|0.134491|\n",
    "|委员会成员 |Has_Committee|41544|0.092609|\n",
    "|高管 | Has_Manager |102271|0.227980|\n",
    "|核心技术人员 |Has_Core_Technical_Staff|20|0.000045|\n",
    "|其他 | Has_Othoer_Manager|119389|0.266139|\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:12:43.471818Z",
     "start_time": "2020-01-28T05:12:43.464854Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts['rel_listed_company_has_manager'] = {'fullname':':START_ID(company)', \n",
    "                                              'hash_cust':':END_ID(manager_id)', \n",
    "                                              'begin_date':'begin_date:long',\n",
    "                                              'end_date':'end_date:long',\n",
    "                                              'title':'title',\n",
    "                                              'lev':':TYPE'\n",
    "                                              }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:10:57.736481Z",
     "start_time": "2020-01-28T05:10:56.221486Z"
    }
   },
   "outputs": [],
   "source": [
    "df_stk_managers['symbol'] = df_stk_managers['ts_code'].apply(lambda x:x.split('.')[0])\n",
    "\n",
    "df_stk_managers['begin_date'] = df_stk_managers['begin_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )\n",
    "df_stk_managers['end_date'] = df_stk_managers['end_date'].apply(lambda x: -1 if pd.isnull(x) else int(x))\n",
    "df_stk_managers['ann_date'] = df_stk_managers['ann_date'].apply(lambda x: -1 if pd.isnull(x) else int(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:11:24.940410Z",
     "start_time": "2020-01-28T05:11:24.544326Z"
    }
   },
   "outputs": [],
   "source": [
    "rel_listed_company_has_manager = pd.merge(df_stk_managers,df_company_lsit_all[['symbol','fullname']],on='symbol',how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:19:12.146760Z",
     "start_time": "2020-01-28T05:19:12.142766Z"
    }
   },
   "outputs": [],
   "source": [
    "# 部分为空的的人员，为退市的人员\n",
    "# temp = rel_listed_company_has_manager[rel_listed_company_has_manager['fullname'].isnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:16:20.958871Z",
     "start_time": "2020-01-28T05:16:20.820851Z"
    }
   },
   "outputs": [],
   "source": [
    "types = 'rel_listed_company_has_manager'\n",
    "\n",
    "rel_listed_company_has_manager = rel_listed_company_has_manager[rel_listed_company_has_manager['fullname'].notnull()&rel_listed_company_has_manager['hash_cust'].notnull()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "rel_listed_company_has_manager = rel_listed_company_has_manager.sort_values(['fullname','hash_cust','title','begin_date','end_date'],ascending=False).drop_duplicates(['fullname','hash_cust','title','begin_date'],keep='first')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ts_code</th>\n",
       "      <th>ann_date</th>\n",
       "      <th>name</th>\n",
       "      <th>gender</th>\n",
       "      <th>lev</th>\n",
       "      <th>title</th>\n",
       "      <th>edu</th>\n",
       "      <th>national</th>\n",
       "      <th>birthday</th>\n",
       "      <th>begin_date</th>\n",
       "      <th>end_date</th>\n",
       "      <th>hash_cust</th>\n",
       "      <th>label</th>\n",
       "      <th>symbol</th>\n",
       "      <th>fullname</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>154169</th>\n",
       "      <td>002601.SZ</td>\n",
       "      <td>20170425</td>\n",
       "      <td>许晓斌</td>\n",
       "      <td>M</td>\n",
       "      <td>其他</td>\n",
       "      <td>薪酬与考核委员会委员</td>\n",
       "      <td>硕士</td>\n",
       "      <td>中国</td>\n",
       "      <td>197701</td>\n",
       "      <td>20140404</td>\n",
       "      <td>-1</td>\n",
       "      <td>f52079b3b6db2cc1508dbc9caa2d678c</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>002601</td>\n",
       "      <td>龙蟒佰利联集团股份有限公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154178</th>\n",
       "      <td>002601.SZ</td>\n",
       "      <td>20170425</td>\n",
       "      <td>许晓斌</td>\n",
       "      <td>M</td>\n",
       "      <td>其他</td>\n",
       "      <td>薪酬与考核委员会召集人</td>\n",
       "      <td>硕士</td>\n",
       "      <td>中国</td>\n",
       "      <td>197701</td>\n",
       "      <td>20140404</td>\n",
       "      <td>-1</td>\n",
       "      <td>f52079b3b6db2cc1508dbc9caa2d678c</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>002601</td>\n",
       "      <td>龙蟒佰利联集团股份有限公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154198</th>\n",
       "      <td>002601.SZ</td>\n",
       "      <td>20170425</td>\n",
       "      <td>许晓斌</td>\n",
       "      <td>M</td>\n",
       "      <td>董事会成员</td>\n",
       "      <td>独立董事</td>\n",
       "      <td>硕士</td>\n",
       "      <td>中国</td>\n",
       "      <td>197701</td>\n",
       "      <td>20140404</td>\n",
       "      <td>-1</td>\n",
       "      <td>f52079b3b6db2cc1508dbc9caa2d678c</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>002601</td>\n",
       "      <td>龙蟒佰利联集团股份有限公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154209</th>\n",
       "      <td>002601.SZ</td>\n",
       "      <td>20140408</td>\n",
       "      <td>许晓斌</td>\n",
       "      <td>M</td>\n",
       "      <td>其他</td>\n",
       "      <td>战略委员会委员</td>\n",
       "      <td>硕士</td>\n",
       "      <td>中国</td>\n",
       "      <td>197701</td>\n",
       "      <td>20140404</td>\n",
       "      <td>20170421</td>\n",
       "      <td>f52079b3b6db2cc1508dbc9caa2d678c</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>002601</td>\n",
       "      <td>龙蟒佰利联集团股份有限公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>154160</th>\n",
       "      <td>002601.SZ</td>\n",
       "      <td>20170425</td>\n",
       "      <td>许晓斌</td>\n",
       "      <td>M</td>\n",
       "      <td>其他</td>\n",
       "      <td>审计委员会委员</td>\n",
       "      <td>硕士</td>\n",
       "      <td>中国</td>\n",
       "      <td>197701</td>\n",
       "      <td>20140404</td>\n",
       "      <td>-1</td>\n",
       "      <td>f52079b3b6db2cc1508dbc9caa2d678c</td>\n",
       "      <td>MANAGER</td>\n",
       "      <td>002601</td>\n",
       "      <td>龙蟒佰利联集团股份有限公司</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          ts_code  ann_date name gender    lev        title edu national  \\\n",
       "154169  002601.SZ  20170425  许晓斌      M     其他   薪酬与考核委员会委员  硕士       中国   \n",
       "154178  002601.SZ  20170425  许晓斌      M     其他  薪酬与考核委员会召集人  硕士       中国   \n",
       "154198  002601.SZ  20170425  许晓斌      M  董事会成员         独立董事  硕士       中国   \n",
       "154209  002601.SZ  20140408  许晓斌      M     其他      战略委员会委员  硕士       中国   \n",
       "154160  002601.SZ  20170425  许晓斌      M     其他      审计委员会委员  硕士       中国   \n",
       "\n",
       "        birthday  begin_date  end_date                         hash_cust  \\\n",
       "154169    197701    20140404        -1  f52079b3b6db2cc1508dbc9caa2d678c   \n",
       "154178    197701    20140404        -1  f52079b3b6db2cc1508dbc9caa2d678c   \n",
       "154198    197701    20140404        -1  f52079b3b6db2cc1508dbc9caa2d678c   \n",
       "154209    197701    20140404  20170421  f52079b3b6db2cc1508dbc9caa2d678c   \n",
       "154160    197701    20140404        -1  f52079b3b6db2cc1508dbc9caa2d678c   \n",
       "\n",
       "          label  symbol       fullname  \n",
       "154169  MANAGER  002601  龙蟒佰利联集团股份有限公司  \n",
       "154178  MANAGER  002601  龙蟒佰利联集团股份有限公司  \n",
       "154198  MANAGER  002601  龙蟒佰利联集团股份有限公司  \n",
       "154209  MANAGER  002601  龙蟒佰利联集团股份有限公司  \n",
       "154160  MANAGER  002601  龙蟒佰利联集团股份有限公司  "
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rel_listed_company_has_manager.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:16:29.483286Z",
     "start_time": "2020-01-28T05:16:25.839286Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  after removing the cwd from sys.path.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data/output/rel_listed_company_has_manager.csv\n",
      "(432429, 6)\n"
     ]
    }
   ],
   "source": [
    "temp = generate_input_files(rel_listed_company_has_manager,dicts=all_dicts[types],types = types,output_path=output_path)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 节点：基金"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:28:07.546459Z",
     "start_time": "2020-01-28T05:28:07.523463Z"
    }
   },
   "outputs": [],
   "source": [
    "df_fund_basic['fund'] = df_fund_basic['ts_code'].apply(lambda x: x.split('.')[0])\n",
    "df_fund_basic['market'] = df_fund_basic['ts_code'].apply(lambda x: x.split('.')[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:28:08.819361Z",
     "start_time": "2020-01-28T05:28:08.735357Z"
    }
   },
   "outputs": [],
   "source": [
    "df_fund_basic['found_date'] = df_fund_basic['found_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )\n",
    "df_fund_basic['due_date'] = df_fund_basic['due_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )\n",
    "df_fund_basic['list_date'] = df_fund_basic['list_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )\n",
    "df_fund_basic['issue_date'] = df_fund_basic['issue_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )\n",
    "df_fund_basic['delist_date'] = df_fund_basic['delist_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:28:09.623336Z",
     "start_time": "2020-01-28T05:28:09.590368Z"
    }
   },
   "outputs": [],
   "source": [
    "df_fund_basic['issue_amount'] = df_fund_basic['issue_amount'].apply(lambda x:float(x))\n",
    "df_fund_basic['m_fee'] = df_fund_basic['m_fee'].apply(lambda x:float(x))\n",
    "df_fund_basic['c_fee'] = df_fund_basic['c_fee'].apply(lambda x:float(x))\n",
    "df_fund_basic['min_amount'] = df_fund_basic['min_amount'].apply(lambda x:float(x))\n",
    "\n",
    "df_fund_basic['duration_year'] = df_fund_basic['duration_year'].apply(lambda x:float(x))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:29:49.572812Z",
     "start_time": "2020-01-28T05:29:49.560821Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts['node_funds'] =  {'fund':'fund_code:ID(fund_code)', \n",
    "                            'name':'name', \n",
    "                            'fund_type':'fund_type', # 投资类型\n",
    "                            'invest_type':'invest_type', # 投资风格\n",
    "                            'type':'type',  # 基金类型 \n",
    "                            'benchmark':'benchmark', # 业绩比较基准\n",
    "                            'market':'market', #  场外'OF', 场内 'SH', 'SZ'\n",
    "                            'found_date':'found_date:long',  # 成立日期\n",
    "                            'delist_date':'delist_date:long',  # 退市日期\n",
    "                            'status':'status',  #  存续状态: D摘牌 I发行 L已上市\n",
    "                            'label':':LABEL' }"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### 数据预处理"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-25T15:55:55.530724Z",
     "start_time": "2020-01-25T15:55:55.523724Z"
    }
   },
   "source": [
    "对于 这几个时间，有两种处理方式：1.作为节点属性；2.作为关系属性\n",
    "\n",
    "- 'found_date'  # 成立日期\n",
    "- 'due_date'    # 到期日期\n",
    "- 'list_date'  # 上市时间\n",
    "- 'issue_date'  # 发行日期\n",
    "- 'delist_date'   # 退市日期\n",
    "\n",
    "每个基金都有唯一的5个时间.\n",
    "但是 因为 Neo4j 不持支对于边添加索引.所以考虑查询效率,将这些属性放于节点上."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:28:31.715870Z",
     "start_time": "2020-01-28T05:28:31.701866Z"
    }
   },
   "outputs": [],
   "source": [
    "types = 'node_funds'\n",
    "\n",
    "df_fund_basic['label'] = 'FUND'\n",
    "node_funds = df_fund_basic[list(all_dicts[types].keys())]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:28:35.444781Z",
     "start_time": "2020-01-28T05:28:35.402785Z"
    }
   },
   "outputs": [],
   "source": [
    "node_funds = node_funds.sort_values(['fund','found_date','delist_date'],ascending=False).drop_duplicates('fund',keep='first')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fund</th>\n",
       "      <th>name</th>\n",
       "      <th>fund_type</th>\n",
       "      <th>invest_type</th>\n",
       "      <th>type</th>\n",
       "      <th>benchmark</th>\n",
       "      <th>market</th>\n",
       "      <th>found_date</th>\n",
       "      <th>delist_date</th>\n",
       "      <th>status</th>\n",
       "      <th>label</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6984</th>\n",
       "      <td>F450005</td>\n",
       "      <td>国富潜力组合H港币</td>\n",
       "      <td>混合型</td>\n",
       "      <td>混合型</td>\n",
       "      <td>契约型开放式</td>\n",
       "      <td>MSCI中国A股指数*85%+中债国债总指数(全价)*10%+同业存款息率*5%</td>\n",
       "      <td>OF</td>\n",
       "      <td>20160229</td>\n",
       "      <td>-1</td>\n",
       "      <td>L</td>\n",
       "      <td>FUND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6985</th>\n",
       "      <td>F450004</td>\n",
       "      <td>国富潜力组合H美元</td>\n",
       "      <td>混合型</td>\n",
       "      <td>混合型</td>\n",
       "      <td>契约型开放式</td>\n",
       "      <td>MSCI中国A股指数*85%+中债国债总指数(全价)*10%+同业存款息率*5%</td>\n",
       "      <td>OF</td>\n",
       "      <td>20160229</td>\n",
       "      <td>-1</td>\n",
       "      <td>L</td>\n",
       "      <td>FUND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6934</th>\n",
       "      <td>F217003</td>\n",
       "      <td>招商安泰债券H</td>\n",
       "      <td>债券型</td>\n",
       "      <td>债券型</td>\n",
       "      <td>契约型开放式</td>\n",
       "      <td>中证国债指数收益率*95%+同业存款利率*5%</td>\n",
       "      <td>OF</td>\n",
       "      <td>20160317</td>\n",
       "      <td>-1</td>\n",
       "      <td>L</td>\n",
       "      <td>FUND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6532</th>\n",
       "      <td>F202108</td>\n",
       "      <td>南方润元纯债H</td>\n",
       "      <td>债券型</td>\n",
       "      <td>债券型</td>\n",
       "      <td>契约型开放式</td>\n",
       "      <td>中证全债指数</td>\n",
       "      <td>OF</td>\n",
       "      <td>20160713</td>\n",
       "      <td>-1</td>\n",
       "      <td>L</td>\n",
       "      <td>FUND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6282</th>\n",
       "      <td>F202017</td>\n",
       "      <td>南方深成ETF联接H</td>\n",
       "      <td>股票型</td>\n",
       "      <td>被动指数型</td>\n",
       "      <td>契约型开放式</td>\n",
       "      <td>深证成份指数收益率*95%+银行活期存款利率(税后)*5%</td>\n",
       "      <td>OF</td>\n",
       "      <td>20160830</td>\n",
       "      <td>-1</td>\n",
       "      <td>L</td>\n",
       "      <td>FUND</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         fund        name fund_type invest_type    type  \\\n",
       "6984  F450005   国富潜力组合H港币       混合型         混合型  契约型开放式   \n",
       "6985  F450004   国富潜力组合H美元       混合型         混合型  契约型开放式   \n",
       "6934  F217003     招商安泰债券H       债券型         债券型  契约型开放式   \n",
       "6532  F202108     南方润元纯债H       债券型         债券型  契约型开放式   \n",
       "6282  F202017  南方深成ETF联接H       股票型       被动指数型  契约型开放式   \n",
       "\n",
       "                                     benchmark market  found_date  \\\n",
       "6984  MSCI中国A股指数*85%+中债国债总指数(全价)*10%+同业存款息率*5%     OF    20160229   \n",
       "6985  MSCI中国A股指数*85%+中债国债总指数(全价)*10%+同业存款息率*5%     OF    20160229   \n",
       "6934                   中证国债指数收益率*95%+同业存款利率*5%     OF    20160317   \n",
       "6532                                    中证全债指数     OF    20160713   \n",
       "6282             深证成份指数收益率*95%+银行活期存款利率(税后)*5%     OF    20160830   \n",
       "\n",
       "      delist_date status label  \n",
       "6984           -1      L  FUND  \n",
       "6985           -1      L  FUND  \n",
       "6934           -1      L  FUND  \n",
       "6532           -1      L  FUND  \n",
       "6282           -1      L  FUND  "
      ]
     },
     "execution_count": 100,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "node_funds.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:28:36.845445Z",
     "start_time": "2020-01-28T05:28:36.624444Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data/output/node_funds.csv\n",
      "(10859, 11)\n"
     ]
    }
   ],
   "source": [
    "temp = generate_input_files(node_funds,dicts=all_dicts[types],types = types,output_path = output_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 关系：基金-->托管人"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = node_companies[['name','fullname']].rename(columns = {'name':'custodian'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "rel_fund_has_custodian = pd.merge(df_fund_basic,temp, on='custodian',how='left')[['fund','fullname']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_dicts['rel_fund_has_custodian'] = {'fund':':START_ID(fund_code)',   # TS基金代码\n",
    "                                      'fullname':':END_ID(company)',   # 股票代码\n",
    "                                      'type':':TYPE'\n",
    "                                     }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "rel_fund_has_custodian['type']='HAS_CUSTODIAN'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data/output/rel_fund_has_custodian.csv\n",
      "(10923, 3)\n"
     ]
    }
   ],
   "source": [
    "types = 'rel_fund_has_custodian'\n",
    "\n",
    "temp = generate_input_files(rel_fund_has_custodian,dicts=all_dicts[types],types = types,output_path = output_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>fund</th>\n",
       "      <td>515070</td>\n",
       "      <td>515330</td>\n",
       "      <td>515310</td>\n",
       "      <td>501089</td>\n",
       "      <td>515080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fullname</th>\n",
       "      <td>中国银行股份有限公司</td>\n",
       "      <td>招商证券股份有限公司</td>\n",
       "      <td>招商银行股份有限公司</td>\n",
       "      <td>中国民生银行股份有限公司</td>\n",
       "      <td>上海浦东发展银行股份有限公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>type</th>\n",
       "      <td>HAS_CUSTODIAN</td>\n",
       "      <td>HAS_CUSTODIAN</td>\n",
       "      <td>HAS_CUSTODIAN</td>\n",
       "      <td>HAS_CUSTODIAN</td>\n",
       "      <td>HAS_CUSTODIAN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      0              1              2              3  \\\n",
       "fund             515070         515330         515310         501089   \n",
       "fullname     中国银行股份有限公司     招商证券股份有限公司     招商银行股份有限公司   中国民生银行股份有限公司   \n",
       "type      HAS_CUSTODIAN  HAS_CUSTODIAN  HAS_CUSTODIAN  HAS_CUSTODIAN   \n",
       "\n",
       "                       4  \n",
       "fund              515080  \n",
       "fullname  上海浦东发展银行股份有限公司  \n",
       "type       HAS_CUSTODIAN  "
      ]
     },
     "execution_count": 107,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rel_fund_has_custodian.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 关系：基金-->管理人"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [],
   "source": [
    "temp = node_companies[['name','fullname']].rename(columns = {'name':'management'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "rel_fund_has_management = pd.merge(df_fund_basic,temp, on='management',how='left')[['fund','fullname']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "all_dicts['rel_fund_has_management'] = {'fund':':START_ID(fund_code)',   # TS基金代码\n",
    "                                      'fullname':':END_ID(company)',   # 股票代码\n",
    "                                      'type':':TYPE'\n",
    "                                     }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "rel_fund_has_management['type']='HAS_MANAGEMENT'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data/output/rel_fund_has_management.csv\n",
      "(10935, 3)\n"
     ]
    }
   ],
   "source": [
    "types = 'rel_fund_has_management'\n",
    "\n",
    "temp = generate_input_files(rel_fund_has_management,dicts=all_dicts[types],types = types,output_path = output_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>fund</th>\n",
       "      <td>515070</td>\n",
       "      <td>515330</td>\n",
       "      <td>515310</td>\n",
       "      <td>501089</td>\n",
       "      <td>515080</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fullname</th>\n",
       "      <td>华夏基金管理有限公司</td>\n",
       "      <td>天弘基金管理有限公司</td>\n",
       "      <td>汇添富基金管理股份有限公司</td>\n",
       "      <td>方正富邦基金管理有限公司</td>\n",
       "      <td>招商基金管理有限公司</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>type</th>\n",
       "      <td>HAS_MANAGEMENT</td>\n",
       "      <td>HAS_MANAGEMENT</td>\n",
       "      <td>HAS_MANAGEMENT</td>\n",
       "      <td>HAS_MANAGEMENT</td>\n",
       "      <td>HAS_MANAGEMENT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                       0               1               2               3  \\\n",
       "fund              515070          515330          515310          501089   \n",
       "fullname      华夏基金管理有限公司      天弘基金管理有限公司   汇添富基金管理股份有限公司    方正富邦基金管理有限公司   \n",
       "type      HAS_MANAGEMENT  HAS_MANAGEMENT  HAS_MANAGEMENT  HAS_MANAGEMENT   \n",
       "\n",
       "                       4  \n",
       "fund              515080  \n",
       "fullname      招商基金管理有限公司  \n",
       "type      HAS_MANAGEMENT  "
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rel_fund_has_management.head().T"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-20T05:41:29.472559Z",
     "start_time": "2020-01-20T05:41:25.448556Z"
    }
   },
   "source": [
    "### 关系: 公募基金持仓数据 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:16:59.107159Z",
     "start_time": "2020-01-28T05:16:54.370937Z"
    }
   },
   "outputs": [],
   "source": [
    "df_fund_portfolio = pd.read_csv(data_path + fund_portfolio_name )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:17:11.873687Z",
     "start_time": "2020-01-28T05:17:10.268694Z"
    }
   },
   "outputs": [],
   "source": [
    "df_fund_portfolio['fund'] = df_fund_portfolio['ts_code'].apply(lambda x: x.split('.')[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:17:14.176314Z",
     "start_time": "2020-01-28T05:17:12.509318Z"
    }
   },
   "outputs": [],
   "source": [
    "df_fund_portfolio['symbol'] = df_fund_portfolio['symbol'].apply(lambda x: x.split('.')[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:27:11.838611Z",
     "start_time": "2020-01-28T05:27:11.833616Z"
    }
   },
   "outputs": [],
   "source": [
    "all_dicts['rel_fund_listed_company_portfolio'] = {'fund':':START_ID(fund_code)',   # TS基金代码\n",
    "                                                  'fullname':':END_ID(company)',   # 股票代码\n",
    "                                                  'ann_date':'ann_date:float',   # 公告日期\n",
    "                                                  'end_date':'end_date:float' ,  # 截止日期\n",
    "                                                  'mkv':'mkv:float' ,  # 持有股票市值(元)\n",
    "                                                  'amount':'amount:float' ,  # 持有股票数量（股）\n",
    "                                                  'stk_mkv_ratio':'stk_mkv_ratio:float',   # 占股票市值比\n",
    "                                                  'stk_float_ratio':'stk_float_ratio:float',   # 占流通股本比例\n",
    "                                                  'type':':TYPE'\n",
    "                                                 }"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:27:17.679041Z",
     "start_time": "2020-01-28T05:27:13.737945Z"
    }
   },
   "outputs": [],
   "source": [
    "df_fund_portfolio['ann_date'] = df_fund_portfolio['ann_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )\n",
    "df_fund_portfolio['end_date'] = df_fund_portfolio['end_date'].apply(lambda x: -1 if pd.isnull(x) else int(x) )\n",
    "\n",
    "df_fund_portfolio['amount'] = df_fund_portfolio['amount'].apply(float)\n",
    "df_fund_portfolio['stk_mkv_ratio'] = df_fund_portfolio['stk_mkv_ratio'].apply(float)\n",
    "df_fund_portfolio['stk_float_ratio'] = df_fund_portfolio['stk_float_ratio'].apply(float)\n",
    "df_fund_portfolio['mkv'] = df_fund_portfolio['mkv'].apply(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:27:18.702938Z",
     "start_time": "2020-01-28T05:27:17.682967Z"
    }
   },
   "outputs": [],
   "source": [
    "rel_fund_listed_company_portfolio = pd.merge(df_fund_portfolio,df_company_lsit_all[['symbol','fullname']],on='symbol',how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:27:20.473971Z",
     "start_time": "2020-01-28T05:27:18.704939Z"
    }
   },
   "outputs": [],
   "source": [
    "rel_fund_listed_company_portfolio = rel_fund_listed_company_portfolio.sort_values(['fund','fullname','ann_date','end_date'],ascending=False).drop_duplicates(['fund','fullname'],keep='first')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:27:20.540027Z",
     "start_time": "2020-01-28T05:27:20.475939Z"
    }
   },
   "outputs": [],
   "source": [
    "rel_fund_listed_company_portfolio['type']='IN_PORTFOLIO'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:27:21.282541Z",
     "start_time": "2020-01-28T05:27:21.276544Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1449174, 11)"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "rel_fund_listed_company_portfolio.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-01-28T05:27:37.372258Z",
     "start_time": "2020-01-28T05:27:21.970218Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/opt/conda/lib/python3.7/site-packages/ipykernel_launcher.py:4: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  after removing the cwd from sys.path.\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "save header {}..\n",
      "save_data ...\n",
      " ../data/output/rel_fund_listed_company_portfolio.csv\n",
      "(1449174, 9)\n"
     ]
    }
   ],
   "source": [
    "types = 'rel_fund_listed_company_portfolio'\n",
    "\n",
    "temp = generate_input_files(rel_fund_listed_company_portfolio,dicts=all_dicts[types],types = types,output_path = output_path)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  },
  "toc": {
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": "block",
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
